﻿using AutoMapper.Execution;
using ExcelProject.Core;
using ExcelProject.Model;
using OfficeOpenXml;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;

namespace EPPluslHelper
{
    public class ExcelOperation
    {
        /// <summary>
        /// 将表格数据转换为指定的数据实体
        /// </summary>
        public static ICollection<T> ExcelToEntity<T>(Stream excelStream, ICollection<ExcelCellOption<T>> options)
        {
            using ExcelPackage pack = new(excelStream);
            var sheet = pack.Workbook.Worksheets[0];
            int rowCount = sheet.Dimension.Rows, colCount = sheet.Dimension.Columns;
            // 获取对应设置的 表头 以及其 column
            var header = sheet.Cells[1, 1, 1, sheet.Dimension.Columns]
            .Where(item => options.Any(opt => opt.ExcelField == item.Value.ToString()))
            .ToDictionary(item => item.Value.ToString(), item => item.End.Column);
            List<T> data = new();
            // 将excel 的数据转换为 对应实体F
            for (int r = 2; r <= rowCount; r++)
            {
                // 将单行数据转换为 表头:数据 的键值对
                var rowData = sheet.Cells[r, 1, r, colCount]
                .Where(item => header.Any(title => title.Value == item.End.Column))
                .Select(item => new KeyValuePair<string, string>(header.First(title => title.Value == item.End.Column).Key, item.Value?.ToString()))
                .ToDictionary(item => item.Key, item => item.Value);
                var obj = Activator.CreateInstance(typeof(T));
                // 根据对应传入的设置 为obj赋值
                foreach (var option in options)
                {
                    if (!string.IsNullOrEmpty(option.ExcelField))
                    {
                        var value = rowData.ContainsKey(option.ExcelField) ? rowData[option.ExcelField] : string.Empty;
                        if (!string.IsNullOrEmpty(value))
                            option.Prop.SetValue(obj, option.Action == null ? value : option.Action(value));
                    }
                    // 可以用来初始化与表格无关的字段 如 创建时间 Guid主键 之类的东西
                    else
                        option.Prop.SetValue(obj, option.Action == null ? null : option.Action(string.Empty));
                }
                data.Add((T)obj);
            }
            return data;
        }


        /// <summary>
        /// 将表格数据转换为指定的数据实体
        /// </summary>
        public static List<T> EPPlusExcelToEntity<T>(ExcelWorksheet workSheet, List<int> tableHeaderX, List<int> tableHeaderY, ICollection<ExcelCellOption<T>> options)
        {

            List<string> Column = new();
            List<T> data = new();
            int rowCount = workSheet.Dimension.Rows, colCount = tableHeaderY.Max();
            #region 合并单元格坐标存储
            //合并单元格的坐标
            List<MergedAddress> MergedCellsList = new();
            //所有合并单元格
            var MergedCells = workSheet.MergedCells;
            StringBuilder stringBuilder = new StringBuilder();
            foreach (var item in MergedCells)
            {

                var cell = workSheet.Cells[item];   //获取合并单元格
                var index = item.IndexOf(":");

                #region 合并单元格基本信息
                MergedAddress merged = new MergedAddress()
                {
                    Address = item,
                    Begin = item.Substring(0, index),
                    End = item.Substring(index, 2),
                    StartRow = cell.Start.Row,
                    StartColumn = cell.Start.Column,
                    EndRow = cell.End.Row,
                    EndColumn = cell.End.Column
                };
                #endregion

                #region 检查类型
                //开始列与结束列相同，则说明是在跨行
                if (cell.Start.Column == cell.End.Column)
                {
                    merged.Type = CellMergeType.跨行;
                }
                //开始行与结束行相同，则说明是在跨列
                if (cell.Start.Row == cell.End.Row)
                {
                    merged.Type = CellMergeType.跨列;
                }
                //起始行与列皆不相同，则说明是在跨行跨列
                if (cell.Start.Column != cell.End.Column && cell.Start.Row != cell.End.Row)
                {
                    merged.Type = CellMergeType.跨行跨列;
                }
                MergedCellsList.Add(merged);
                #endregion

                //stringBuilder.AppendLine(cell.Text+" | "+item);
            }

            #endregion

            #region 读取表格,按行读取
            //循环列(m轴)
            for (int m = workSheet.Dimension.Start.Row, n = rowCount; m <= n; m++)
            {
                Dictionary<string, string> keyValuePairs = new Dictionary<string, string>();

                //循环行(y轴)
                for (int j = workSheet.Dimension.Start.Column, k = colCount; j <= k; j++)
                {
                    var Cell = workSheet.Cells[m, j];      //单元格
                    var CellValue = workSheet.Cells[m, j].Value;        //单元格值


                    if (CellValue != null)
                    {
                        //是否是表头
                        if (tableHeaderX.Contains(m) && tableHeaderY.Contains(j))
                        {
                            if (Cell.Merge)
                            {
                                //txtMsg.Text += $"\n{m}行{j}列 | 合并单元格 | 列名称:{Replace(CellValue)} ";
                            }
                            else
                            {
                                //包含在表头数组的话

                                //如果我的上一行不是跨列，那么我大概率是数据，而不是表头
                                int x = m - 1;      //行-1，找当前单元格上面的格子
                                int y = j;          //列

                                //取到跨列的坐标集,测试数据暂且用Fist取，实际会存在多个坐标对象
                                var parent = MergedCellsList.FirstOrDefault(c => c.Type == CellMergeType.跨列 && x == c.StartRow && (y >= c.StartColumn && y <= c.EndColumn));
                                if (parent != null)
                                {
                                    //如果我的上一行单元格是跨列，那么我就要变成子集。
                                    //txtMsg.Text += $"\n{m}行{j}列 | 非合并单元格 | 列名称:{Replace(CellValue)} | 父级信息:[{workSheet.Cells[parent.Address].Text}]";
                                    Column.Add(Replace($"{workSheet.Cells[parent.Address].Text}_{CellValue}"));
                                    continue;
                                }
                                else
                                {

                                    //txtMsg.Text += $"\n{m}行{j}列 | 非合并单元格 | 列名称:{Replace(CellValue)}";
                                }

                                //可依次判断跨行等状态的合并单元格子集
                            }
                            Column.Add(Replace($"{CellValue}"));

                        }
                        //大于X轴或者大于Y轴则说明是数据
                        //大于X轴或者大于Y轴则说明是数据
                        else if (m <= colCount && m >= tableHeaderX.Max() || j <= rowCount && m >= tableHeaderY.Max())
                        {
                            keyValuePairs.Add(Column[Cell.Start.Column - 1], Replace($"{Replace($"{CellValue}")}"));
                        }

                    }

                }
                var obj = Activator.CreateInstance(typeof(T));
                foreach (var option in options)
                {
                    if (!string.IsNullOrEmpty(option.ExcelField))
                    {
                        var value = keyValuePairs.ContainsKey(option.ExcelField) ? keyValuePairs[option.ExcelField] : string.Empty;
                        if (!string.IsNullOrEmpty(value))
                            option.Prop.SetValue(obj, option.Action == null ? value : option.Action(value));
                    }
                    // 可以用来初始化与表格无关的字段 如 创建时间 Guid主键 之类的东西
                    else
                        option.Prop.SetValue(obj, option.Action == null ? null : option.Action(string.Empty));
                }
                data.Add((T)obj);
            }


            #endregion

            //int rowCount = workSheet.Dimension.Rows, colCount = workSheet.Dimension.Columns;
            //// 获取对应设置的 表头 以及其 column
            //var header = workSheet.Cells[4, 1, 5, 11]
            //.Where(item => options.Any(opt => opt.ExcelField == item.Value?.ToString()))
            //.ToDictionary(item => item.Value.ToString(), item => item.End.Column);

            ////将excel 的数据转换为 对应实体F
            //for (int r = tableHeaderX.Max(); r <= rowCount; r++)
            //{
            //    // 将单行数据转换为 表头:数据 的键值对
            //    var rowData = workSheet.Cells[6, 1, 61, 11]
            //    .Where(item => header.Any(c => c.Value == item.End.Column))
            //    .Select(item => new KeyValuePair<string, string>(header.First(title => title.Value == item.End.Column).Key, item.Value?.ToString()))
            //    .ToDictionary(item => item.Key, item => item.Value);
            //    var obj = Activator.CreateInstance(typeof(T));
            //    // 根据对应传入的设置 为obj赋值
            //    foreach (var option in options)
            //    {
            //        if (!string.IsNullOrEmpty(option.ExcelField))
            //        {
            //            var value = rowData.ContainsKey(option.ExcelField) ? rowData[option.ExcelField] : string.Empty;
            //            if (!string.IsNullOrEmpty(value))
            //                option.Prop.SetValue(obj, option.Action == null ? value : option.Action(value));
            //        }
            //        // 可以用来初始化与表格无关的字段 如 创建时间 Guid主键 之类的东西
            //        else
            //            option.Prop.SetValue(obj, option.Action == null ? null : option.Action(string.Empty));
            //    }
            //    data.Add((T)obj);
            //}
            return data;
        }

        private static string Replace(object str)
        {
            if (str != null)
            {
                return str.ToString().Replace("\n", "");
            }
            return "";
        }
    }
    public class ExcelCellOption<T>
    {
        /// <summary>
        /// 对应excel中的header字段
        /// </summary>
        public string ExcelField { get; set; }
        /// <summary>
        /// 对应字段的属性(实际上包含PropName)
        /// </summary>
        public PropertyInfo Prop { get; set; }
        /// <summary>
        /// 就是一个看起来比较方便的标识
        /// </summary>
        public string PropName { get; set; }
        /// <summary>
        /// 转换 表格 数据的方法
        /// </summary>
        public Func<string, object> Action { get; set; }
        public static ICollection<ExcelCellOption<T>> GenExcelOption<E>(Expression<Func<T, E>> prop, Func<string, object> action = null)
        {
            var member = prop.GetMember();
            return new List<ExcelCellOption<T>>{
               
            };
        }
    }

    public static class ExcelOptionAdd
    {
        public static ICollection<ExcelCellOption<T>> AddOption<T>(this ICollection<ExcelCellOption<T>> origin, PropertyInfo prop, Func<string, object> action = null)
        {
     
            if (origin == null)
            {
                return new List<ExcelCellOption<T>>{
                    new ExcelCellOption<T>
                    {
                        PropName = prop.Name,
                        Prop = prop,
                        ExcelField = CellExtend.GetCellName(prop),
                        Action = action
                    }
                };
            }
            origin.Add(new ExcelCellOption<T>
            {
                PropName = prop.Name,
                Prop = prop,
                ExcelField = CellExtend.GetCellName(prop),
                Action = action
            });
            return origin;
        }
    }
}
